import fitz  # PyMuPDF
from PIL import Image, ImageDraw, ImageFont
import io
import os
import random
import shutil  # 新增导入

def create_text_image(text, font_path, fontsize=16, text_color=(255, 0, 0), scale=4):
    # 生成高清文字图片
    font = ImageFont.truetype(font_path, fontsize * scale)
    dummy_img = Image.new("RGB", (1, 1))
    draw = ImageDraw.Draw(dummy_img)
    bbox = draw.textbbox((0, 0), text, font=font)
    w = bbox[2] - bbox[0]
    h = bbox[3] - bbox[1]

    img = Image.new("RGBA", (w + 10 * scale, h + 10 * scale), (255, 255, 255, 0))
    draw = ImageDraw.Draw(img)
    draw.text((5 * scale, 5 * scale), text, font=font, fill=text_color + (255,))
    return img, w, h


from openpyxl import load_workbook
from openpyxl.styles import Font

def is_merged_cell(ws, row, col):
    """检查指定位置是否是合并单元格"""
    cell_str = f"{get_column_letter(col)}{row}"
    for merged_range in ws.merged_cells.ranges:
        if cell_str in merged_range:
            return True
    return False

from openpyxl.utils import get_column_letter

from openpyxl.worksheet.worksheet import Worksheet  # 添加这行

def insert_score_and_comment_with_excel(excel_path, output_path, score, comment):
    try:
        wb = load_workbook(excel_path)

        # ✅ 获取第一个普通工作表（跳过 Chartsheet）
        ws = None
        for sheet in wb.worksheets:
            if isinstance(sheet, Worksheet):
                ws = sheet
                break

        if ws is None:
            print(f"跳过非普通工作表文件: {excel_path}，无可处理的 Worksheet。")
            return 0

        red_font = Font(color="FF0000")

        col = 1
        while True:
            cell = ws.cell(row=1, column=col)
            if not is_merged_cell(ws, 1, col) and (cell.value is None or str(cell.value).strip() == ""):
                break
            col += 1

        ws.cell(row=1, column=col, value="成绩:").font = red_font
        ws.cell(row=1, column=col + 1, value=str(score)).font = red_font
        ws.cell(row=1, column=col + 2, value="评语:").font = red_font
        ws.cell(row=1, column=col + 3, value=str(comment)).font = red_font

        wb.save(output_path)
        print(f"保存成功：{output_path}")
        return 1

    except Exception as e:
        print(f"处理失败：{excel_path}，错误类型：{type(e).__name__}\nTraceback: {e}")
        return 0




def deal_one(excel_path, output_path, comment, score):
    return insert_score_and_comment_with_excel(
        excel_path, output_path, score, comment
    )

import os
import random

def batch_process_excels(
    input_dir,
    output_dir,
    good_comments,
    bad_comments,
    good_student_names,
    good_score_range=(97, 99),
    bad_score_range=(94, 96),
    unprocessed_dir=None
):
    os.makedirs(output_dir, exist_ok=True)

    if unprocessed_dir:
        os.makedirs(unprocessed_dir, exist_ok=True)

    results = []

    for filename in os.listdir(input_dir):
        if filename.endswith(".xlsx"):
            excel_path = os.path.join(input_dir, filename)
            output_path = os.path.join(output_dir, filename)

            student_name = filename

            if any(name in student_name for name in good_student_names):

                score = random.randint(*good_score_range)
                comment = random.choice(good_comments)
            else:
                score = random.randint(*bad_score_range)
                comment = random.choice(bad_comments)

            result_flag = deal_one(excel_path, output_path, comment, score)

            if result_flag == 0:
                score = 0
                if unprocessed_dir:
                    shutil.copy(excel_path, os.path.join(unprocessed_dir, filename))

            results.append((student_name, score))

    return results



# 示例用法
if __name__ == "__main__":
    input_dir = "/Users/anyi/Desktop/2/Excel作业"  # 原始PDF文件夹路径
    output_dir = "/Users/anyi/Desktop/2/2_out/Excel作业"  # 输出文件夹路径
    unprocessed_dir = "/Users/anyi/Desktop/2/2_out/Excel作业/未处理"
    comments_better = [
        (
            "Excel数据处理过程逻辑清晰，函数使用熟练，能准确完成多项任务。\n"
            "图表选型合理，展示效果良好，能有效辅助分析结果。实验报告条理清晰，\n"
            "格式规范，语言表达流畅，体现出扎实的数据分析能力与文档写作水平。\n"
        ),
        (
            "能熟练使用Excel函数进行数据处理、分类与统计，过程严谨有序。\n"
            "图表制作整洁美观，表达直观明确，内容分析深入，结果可信度高。\n"
            "报告整体结构合理，表达准确，展现出较高的分析与总结能力。\n"
        ),
        (
            "数据整理完整，处理步骤合理，函数与筛选应用得当，能有效支持后续分析。\n"
            "图表制作精细，风格统一，具有良好的数据呈现能力。\n"
            "报告整体表现出色，条理清晰，逻辑性强，语言规范，展示出良好专业素养。\n"
        ),
        (
            "数据处理过程严谨，Excel功能运用得当，体现出扎实的基础与较强的动手能力。\n"
            "图表设计美观，清晰表达数据变化趋势。报告内容丰富、条理清楚，\n"
            "格式正确，语言规范，整体质量较高。\n"
        ),
        (
            "数据整理与分析详实到位，能灵活运用多种Excel功能辅助决策。\n"
            "图表表达清晰，设计合理，结果分析有逻辑、有深度。\n"
            "实验报告结构紧凑，表述简洁，文档规范性高，充分展示了良好的综合能力。\n"
        )
    ]

    comments_middle = [
        (
            "能完成数据处理基本流程，Excel函数使用较为熟练，\n"
            "但处理步骤略显粗糙，细节处理不够完善。图表表达一般，\n"
            "在呈现重点信息方面仍有提升空间。报告结构较为合理，语言表达需加强。\n"
        ),
        (
            "数据处理步骤基本合理，Excel操作较为熟悉，但部分环节存在疏漏，\n"
            "图表表达清晰度略有欠缺。分析内容略显浅显，与结果结合不够紧密，\n"
            "报告格式基本规范，仍有进一步提升空间。\n"
        ),
        (
            "能够完成基本的数据处理与图表制作任务，但在函数使用、\n"
            "数据筛选等细节上表现一般，未充分发挥Excel优势。\n"
            "报告内容完整性尚可，语言表达较为口语化，建议加强学术性表达训练。\n"
        ),
        (
            "数据处理能力基本达标，函数调用与格式设置有待规范化。\n"
            "图表制作较为简略，缺乏对核心数据的突出展示。\n"
            "实验报告表达不够连贯，部分内容逻辑跳跃，整体完成度尚可。\n"
        ),
        (
            "基本掌握Excel使用方法，能完成指定任务，但缺乏深入分析与总结。\n"
            "图表较为简单，信息密度低，无法有效支持分析观点。\n"
            "报告结构较为松散，内容组织需进一步优化，语言表达不够规范。\n"
        )
    ]


    good_student_name = [
        "张三", "李四", "王五", "赵六", "孙七", "周八", "吴九",
        "郑十", "钱一", "冯二", "陈三", "褚四", "卫五",
        "蒋六", "沈七", "韩八", "杨九"
    ]
    result_list = batch_process_excels(
        input_dir, output_dir,
        comments_better, comments_middle,
        good_student_name,
        unprocessed_dir=unprocessed_dir
    )

    for name, score in result_list:
        print(f"{name}: {score}")

    with open(os.path.join(output_dir, "score_list.csv"), "w", encoding="utf-8") as f:
        f.write("姓名,分数\n")
        for name, score in result_list:
            f.write(f"{name},{score}\n")




